package com.webshop.DataDao;

import com.webshop.JavaBean.Category;
import com.webshop.JavaBean.Goods;
import com.webshop.JavaBean.Goods_attr;
import com.webshop.JavaBean.Picture;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class GoodsDao {
    QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());


    /**
     * 返回对应ID商品的所有详细的属性。
     * 当然库存是大于0的
     * 例如小米8 返回 4G，8G等的商品。
     * @param ProductID
     * @return
     */
    public List<Goods_attr> backDetailGoods(String ProductID) {
        String sql = "select * from goods_attr where ProductID = ? and stock > 0";

        List<Goods_attr> attrGoods = null;
        try {
            attrGoods = qr.query(sql, new BeanListHandler<>(Goods_attr.class), ProductID);

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return attrGoods;

    }


    /**
     * 首页返回热销商品，只有6个。
     * @return
     */
    public List<Goods> isHot() {
        String sql = "select * from goods where isHot = 1 and state=1 limit 0,6 ";
        List<Goods> query = null;
        try {
            query = qr.query(sql, new BeanListHandler<>(Goods.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query;
    }


    /**
     * 分页显示热销数量。
     * @param NumsofProfuct 每页显示数量
     * @param page //这是第几页
     * @return
     */
    public List<Goods> moreIsHot(int NumsofProfuct , int page) {
        String sql = "select * from goods where isHot = 1 and state=1 limit ?,? ";
        List<Goods> query = null;
        int begin = (page - 1) * NumsofProfuct;
        int end = begin + NumsofProfuct;
        try {
            query = qr.query(sql, new BeanListHandler<>(Goods.class), begin, end);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query;
    }

    /**
     *
     * @param name 产品名称
     * @param NumsofProfuct 每页显示数量
     * @param page 显示页数
     * @return
     * @throws Exception
     */
    public List<Goods> searchGoods(String name,int NumsofProfuct , int page )  throws Exception{
        //包含查询（可能有问题）
        int begin = (page - 1) * NumsofProfuct;
        int end = begin + NumsofProfuct;
        String sql = "select * from goods where productName like '%" + name + "%' limit ?,?";
        List<Goods> goods = qr.query(sql, new BeanListHandler<>(Goods.class),begin,end);
        return goods;
    }

    /**
     * 辅助类
     * 返回符合商品类别的商品。
     * 本类应该返回的是本类及所有子类的商品。
     * 自顶向下
     * @return
     */
    private List<Goods> categoryProduct(String category) {
        String sql = "select * from goods where category = ? ";
        List<Goods> goods = new ArrayList<>();
        try {
            goods.addAll(qr.query(sql, new BeanListHandler<>(Goods.class), category));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goods;

    }


    /**
     * 递归查询，总体返回类别下的所有商品。
     * @param CategoryName
     * @return
     */
    public List<Goods> subProducts(String CategoryName) {
        String category = backCId(CategoryName);
        List<Goods> back = new ArrayList<>();
        String sql = "select * from category where pid = ?";
        List<Category> obj = null;
        try {
            //递归查询所有商品
            obj = qr.query(sql, new BeanListHandler<>(Category.class), category);
            for (int i = 0; i < obj.size(); i++) {
               back.addAll(subProducts(obj.get(i).getCname()+"")) ;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        back.addAll(categoryProduct(category));

        return back;
    }


    /**
     * 根据 类型 名称返回 CID
     * 辅助类
     * @param name
     * @return
     */
    private String backCId(String name) {
        System.out.println("商品类名"+name);
        String sql = "select * from category where Cname = ?";
        Category ca = new Category();
        try {
            ca = qr.query(sql, new BeanHandler<>(Category.class), name);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ca.getCID()+"";
    }


    /**
     * 根据 类型 名称返回 PID
     * @param name
     * @return
     */
    public String backPId(String name) {
        String sql = "select * from category where Cname = ?";
        Category ca = new Category();
        try {
            ca = qr.query(sql, new BeanHandler<>(Category.class), name);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ca.getPid()+"";
    }
    /**
     * 返回所有的父类
     * @param pid
     * @return
     */
    public List<Category> backCategory(String pid) {
        List<Category> categories = new ArrayList<>();
        if ("0".equals(pid)) {
            return categories;
        }
        categories = new ArrayList<>();
        String sql = "select * from category where CID  = ?";
        Category query = null;
        try {
            query = qr.query(sql, new BeanHandler<>(Category.class), pid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        categories.add(0, query);
        categories.addAll(0, backCategory(query.getPid() + ""));
        return categories;
    }

    /**
     * 返回商品的所有子图
     * service 层里放进Goods里
     * @param ID
     * @return
     */
    public List<Picture> backPic(int ID) {
        String sql = "SELECT * FROM `picture` WHERE goodsID = ? ";
        List<Picture> query = null;
        try {
            query = qr.query(sql, new BeanListHandler<>(Picture.class), ID);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query;
    }



    /**
     * 返回商品后台图片的路径
     * @param PeoductID
     * @param AttrID
     * @return
     */
    public String backGoodsAttrPic(int PeoductID,int AttrID) {
        String sql = "SELECT * FROM `picture` WHERE goodsID =? AND goodsAttrID = ?";
        Picture query = null;
        try {
            query = qr.query(sql, new BeanHandler<>(Picture.class), PeoductID, AttrID);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        //因为数据库数据有问题，所以需要进行一次判断
        if (query == null) {
            return "";
        }
        String path = query.getPath();

        System.out.println("我要打印了" + path);
        return path;

    }


    /**
     * 返回单个商品的信息，商品详情页要用。
     * @param ID
     * @return
     */
    public Goods backGood(String ID) {
        String sql = "SELECT * FROM `goods` WHERE ID =?";
        Goods query = null;
        try {
            query = qr.query(sql, new BeanHandler<>(Goods.class), ID);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query;
    }

    /**
     * 返回单个商品的详细信息。
     * @param ID
     * @return
     */
    public Goods_attr backGoodAttr(String ID) {
        String sql = "SELECT * FROM `goods_attr` WHERE ID= ?";
        Goods_attr query = null;
        try {
            query = qr.query(sql, new BeanHandler<>(Goods_attr.class), ID);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query;
    }

    /**
     * 更新库存数量
     * @param ID
     * @param num
     */
    public void changeStock(String ID, String num) {
        String sql = "UPDATE goods_attr set stock = ? WHERE ID=?";
        try {
            qr.update(sql, num, ID);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    /**
     * 根据商品名唯一的一个商品属性
     * @param ID
     * @return
     */
    public Goods_attr ga(String ID) {
        String sql = "select * from `goods_attr` where  ProductID  = ? LIMIT 0,1;";

        Goods_attr ga = null;
        try {
            ga = qr.query(sql, new BeanHandler<>(Goods_attr.class), ID);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ga;
    }
}
